package com.buobao.mybatis;

import com.buobao.mybatis.dto.GoodDTO;
import com.buobao.mybatis.entity.Goods;
import com.buobao.mybatis.entity.GoodsDetail;
import com.buobao.mybatis.utils.MyBatisUtils;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.PrintStream;
import java.io.Reader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.function.Consumer;
import java.util.logging.Logger;

public class MyBatisTestor {
    private static PrintStream out;
    static {
        try {
            out = new PrintStream(System.out, true, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }
    /**
     * mybatis的一个基本连接示例
     */
    @Test
    public void testSqlSessionFactory() {
        try {
            //读取配置文件，这里配置文件默认是在resources路径下
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            System.out.println("session factory load success");

            SqlSession sqlSession = null;
            try {
                sqlSession = sqlSessionFactory.openSession();
                Connection connection = sqlSession.getConnection();
                System.out.println(connection);
            } catch (Exception e) {
                throw e;
            } finally {
                if (sqlSession != null) {
                    sqlSession.close();
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void testMyBatisUtils() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            Connection connection = sqlSession.getConnection();
            System.out.println(connection);
//            connection.prepareStatement()
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

    @Test
    public void testMyBatisSelectAll(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            List<Goods> list = sqlSession.selectList("goods.selectAll");

            for (Goods goods:list) {
                out.println(goods.getTitle());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

    /**
     * 单参数查询
     */
    @Test
    public void testMyBatisSelectById() {
        SqlSession sqlSession = null;
        try{
            sqlSession = MyBatisUtils.openSession();
            Goods goods = sqlSession.selectOne("goods.selectById",2676);
            out.println(goods.getTitle());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

    /**
     * 一个lambda封装示例
     * @param comsumer
     */
    private void query(Consumer<SqlSession> comsumer) {
        SqlSession sqlSession = null;
        try{
            sqlSession = MyBatisUtils.openSession();
            comsumer.accept(sqlSession);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

    /**
     * 模糊匹配
     */
    @Test
    public void testMyBatisSelectByName() {
        query(sqlSession -> {
            List<Goods> list = sqlSession.selectList("goods.selectByName","贝乐乐");
            for (Goods goods:list) {
                out.println(goods.getTitle());
            }
        });
    }

    /**
     * 多参数查询
     */
    @Test
    public void testMyBatisSelectByRange() {
        SqlSession sqlSession = null;
        try{
            sqlSession = MyBatisUtils.openSession();
            List<Goods> list = sqlSession.selectList("goods.selectByPriceRange",new HashMap<String,Object>(){{
                put("min",100);
                put("max",5000);
                put("limit",20);
            }});
            for (Goods goods:list) {
                out.println(goods.getTitle()+" "+goods.getCurrentPrice());
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }


    /**
     * 多表查询
     */
    @Test
    public void testMyBatisSelectFromMutiTable() {
        query(sqlSession -> {
            List<HashMap<String,Object>> list = sqlSession.selectList("goods.selectGoodsMap");
            for (HashMap<String,Object> map:list) {
                out.println(map);
            }
        });
    }

    /**
     * 自定义resultmap映射
     */
    @Test
    public void testMyBatisSelectByResultMap(){
        query(sqlSession -> {
            List<GoodDTO> list = sqlSession.selectList("goods.selectGoodsResultMap");
            for (GoodDTO goodDTO:list) {
                out.println(goodDTO);
            }
        });
    }

    @Test
    public void testMyBatisInsert() {
        SqlSession sqlSession = null;
        try{
            sqlSession = MyBatisUtils.openSession();
            Goods goods = new Goods();
            goods.setTitle("this is my inset goods");
            goods.setSubTitle("this is inserted goods' subtitle");
            goods.setOriginalCost(200f);
            goods.setDiscount(50f);
            goods.setCurrentPrice(150f);
            goods.setFreeDelivery(1);
            goods.setCategoryId(43);
            int result = sqlSession.insert("goods.insertGoods",goods);
            out.println("result:"+result);
            out.println(goods.getGoodsId());
            sqlSession.commit();//这个是需要提交下的!
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

    @Test
    public void testMyBatisUpdate() {
        query(sqlSession -> {
            Goods goods = sqlSession.selectOne("goods.selectById",2676);
            goods.setTitle("标题更新喽!");
            int result = sqlSession.update("goods.updateGoods",goods);
            sqlSession.commit();
            out.println("result:"+result);
        });
    }

    @Test
    public void testMyBatisDelete() {
        SqlSession sqlSession = null;
        try{
            sqlSession = MyBatisUtils.openSession();
            Goods goods = sqlSession.selectOne("goods.selectById",2677);
            int result = sqlSession.delete("goods.deleteGoods",goods);
            sqlSession.commit();
            out.println("result:"+result);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

    /**
     * 编译传值和替换传值测试
     */
    @Test
    public void test$query(){
        query(sqlSession -> {
            List<Goods> list = sqlSession.selectList("goods.selectGoodsByCategoryId",new HashMap<String,Object>(){
                {
                    put("categoryId",43);
                    put("orderby","order by current_price desc");
                }
            });
            list.stream().forEach(s-> out.println(s));
        });
    }


    Logger logger = Logger.getLogger(MyBatisTestor.class.getName());


    /**
     * 动态条件查询
     */
    @Test
    public void testDynamicQuery(){
        SqlSession sqlSession = null;

        try{
            sqlSession = MyBatisUtils.openSession();
            List<Goods> list = sqlSession.selectList("goods.selectDynamic",new HashMap<String,Object>(){
                {
                    put("categoryId",43);
                    put("currentPrice",200);
                }
            });
//            list.stream().forEach(s-> logger.log(Level.SEVERE,s.toString()));
            list.stream().forEach(s-> out.println(s));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

    /**
     * 一级缓存测试
     */
    @Test
    public void testMyBatisCacheLevel1(){
//        query(sqlSession -> {
//            Goods goods = sqlSession.selectOne("goods.selectById",1603);
////            sqlSession.commit();
//            Goods goods1 = sqlSession.selectOne("goods.selectById",1603);
//            //这里查询出来的两个对象是一样的，说明只执行了一次查询
//            //一级缓存是自动打开的，在一个session中结果是共享的
//            //但是在两个session中就不是共享的了
//            out.println(goods.hashCode());
//            out.println(goods1.hashCode());
//        });

        query(sqlSession -> {
            Goods goods = sqlSession.selectOne("goods.selectById",1603);
            out.println(goods.hashCode());
        });

        query(sqlSession -> {
            Goods goods = sqlSession.selectOne("goods.selectById",1603);
            out.println(goods.hashCode());
        });

        query(sqlSession -> {
            Goods goods = sqlSession.selectOne("goods.selectById",1603);
            out.println(goods.hashCode());
        });
    }


    /**
     * onetomany级联查询
     */
    @Test
    public void testMyBatisOneToMany(){
        query(sqlSession -> {
            Goods goods = sqlSession.selectOne("goods.selectOneToMany");
            out.println(goods);
        });
    }

    /**
     * manytoone级联查询
     */
    @Test
    public void testMyBatisManyToOne(){
        query(sqlSession -> {
            GoodsDetail goodsDetail = sqlSession.selectOne("goodsDetail.selectManyToOne",9010);
            out.println(goodsDetail);
        });
    }


    /**
     * 分页插件测试
     */
    @Test
    public void testMyBatisPageHelper(){
        query(sqlSession -> {
            PageHelper.startPage(2,10);
            Page page = (Page) sqlSession.selectList("goods.selectByPage");
            out.println(page.getPages()+" "+page.getTotal()+page.getResult());
        });
    }

    /**
     * 批量插入示例
     */
    @Test
    public void testMyBatisBatchInsert(){
        query(sqlSession -> {
            long start = System.currentTimeMillis();
            List<Goods> list = new ArrayList<>();
            //这里也要考虑插入数据太多会导致sql过长也会导致插入失败，这种情况需要循环批量插入
            for (int i=0;i<10000;i++) {
                Goods goods = new Goods();
                goods.setTitle("goods"+i);
                goods.setSubTitle("subtitle"+i);
                goods.setOriginalCost(100f);
                goods.setCategoryId(43);
                goods.setDiscount(20f);
                goods.setCurrentPrice(80f);
                goods.setFreeDelivery(1);
                list.add(goods);
            }
            int result = sqlSession.insert("goods.batchInsert",list);
            sqlSession.commit();
            out.println("result:"+result+" "+(System.currentTimeMillis()-start));
        });
    }

    @Test
    public void testMyBatisBatchDelete(){
        query(sqlSession -> {
            List<Integer> indexs = new ArrayList<>();
            for (int i=12679;i<22679;i++) {
                indexs.add(i);
            }
            int result = sqlSession.delete("goods.batchDelete",indexs);
            sqlSession.commit();
            out.println("result:"+result);
        });
    }
}
















